﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using EHTWebsiteProject.Code.Entity;
using System.Data.SqlClient;
using System.Data;

using EHTWebsite.Code;

namespace EHTWebsiteProject.Code.Entity
{
    public class ProductProvider
    {
        #region Quản lý Product

        public static void UIProduct(Product theProduct, Boolean isAdd)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_IUProducts";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@ProductID", theProduct.ProductID));
            command.Parameters.Add(new SqlParameter("@SupplierID", theProduct.SupplierID));
            command.Parameters.Add(new SqlParameter("@CategoryID", theProduct.CategoryID));
            command.Parameters.Add(new SqlParameter("@ProductName", theProduct.ProductName));
            command.Parameters.Add(new SqlParameter("@QuantityPerUnit", theProduct.QuantityPerUnit));
            command.Parameters.Add(new SqlParameter("@Price", theProduct.Price));
            command.Parameters.Add(new SqlParameter("@PricePerUnit", theProduct.PricePerUnit));
            command.Parameters.Add(new SqlParameter("@PictureUrl", theProduct.PictureUrl));
            command.Parameters.Add(new SqlParameter("@UnitsInStock", theProduct.UnitsInStock));
            command.Parameters.Add(new SqlParameter("@Discount", theProduct.Discount));
            command.Parameters.Add(new SqlParameter("@Description", theProduct.Description));
            command.Parameters.Add(new SqlParameter("@DeletedFlag", theProduct.DeletedFlag));
            command.Parameters.Add(new SqlParameter("@Status", theProduct.Status));
            command.Parameters.Add(new SqlParameter("@NumberOrder", theProduct.NumberOrder));
            command.Parameters.Add(new SqlParameter("@IsAdd", isAdd));
            DataProvider.ExecuteNoneQuery(command);
        }

        public static void DeleteProductsByProductID(Int32 ProductID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_DeleteProductsByProductID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@ProductID", ProductID));
            DataProvider.ExecuteNoneQuery(command);
        }

        // lay san pham theo ID
        public static Product GetProductsByProductID(Int32 ProductID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetProductsByProductID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@ProductID", ProductID));
            DataSet ds = DataProvider.ExecuteDataSet(command);
            Product theProduct = null;

            if (ds.Tables[0].Rows.Count > 0)
            {
                theProduct = new Product();
                theProduct.DeletedFlag = Boolean.Parse(ds.Tables[0].Rows[0]["DeletedFlag"].ToString());
                theProduct.ProductID = Int32.Parse(ds.Tables[0].Rows[0]["ProductID"].ToString());
                theProduct.SupplierID = Int32.Parse(ds.Tables[0].Rows[0]["SupplierID"].ToString());
                theProduct.CategoryID = Int32.Parse(ds.Tables[0].Rows[0]["CategoryID"].ToString());
                theProduct.ProductName = ds.Tables[0].Rows[0]["ProductName"].ToString();
                theProduct.QuantityPerUnit = ds.Tables[0].Rows[0]["QuantityPerUnit"].ToString();
                theProduct.Price = float.Parse(ds.Tables[0].Rows[0]["Price"].ToString());
                theProduct.PictureUrl = ds.Tables[0].Rows[0]["PictureUrl"].ToString();
                theProduct.UnitsInStock = ds.Tables[0].Rows[0]["UnitsInStock"].ToString();
                theProduct.Description = ds.Tables[0].Rows[0]["Description"].ToString();
                theProduct.Discount = float.Parse(ds.Tables[0].Rows[0]["Discount"].ToString());
                theProduct.NumberOrder = Int32.Parse(ds.Tables[0].Rows[0]["NumberOrder"].ToString());
                theProduct.Status = Boolean.Parse(ds.Tables[0].Rows[0]["Status"].ToString());
            }

            return theProduct;
        }

        //lay san pham theo categoryID co phan trang
        public static DataSet GetProductsByCategoryID(Int32 categoryID, Int32 pageIndex, Int32 pageSize, out Int32 totalRecord)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetProductsByCategoryID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@CategoryID", categoryID));
            command.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            command.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            DataSet ds = DataProvider.ExecuteDataSet(command, out totalRecord);
            return ds;
        }

        // lấy tất cả sản phẩm theo CategoryID de export file excel
        public static DataSet GetAllProductByCategoryID(Int32 categoryID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetAllProductByCategoryID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@CategoryID", categoryID));
            DataSet ds = DataProvider.ExecuteDataSet(command);
            return ds;
        }

        // lấy product theo ten
        public static DataSet GetProductsByProductName(string productName, Int32 pageIndex, Int32 pageSize, out Int32 totalRecord)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetProductsByProductName";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@ProductName", productName));
            command.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            command.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            DataSet ds = DataProvider.ExecuteDataSet(command, out totalRecord);
            return ds;
        }

        // kiem tra ton tai cua ten san pham
        public static DataSet CheckExist(string productName)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "sp_CheckExistProductName";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@ProductName", productName));
            DataSet ds = DataProvider.ExecuteDataSet(command);
            return ds;
        }

        //Import data tu file excel
        public static void ImportData(Product theProduct, Boolean isAdd)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_ImportProducts";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@ProductID", theProduct.ProductID));
            command.Parameters.Add(new SqlParameter("@CategoryID", theProduct.CategoryID));
            command.Parameters.Add(new SqlParameter("@ProductName", theProduct.ProductName));
            command.Parameters.Add(new SqlParameter("@QuantityPerUnit", theProduct.QuantityPerUnit));
            command.Parameters.Add(new SqlParameter("@Price", theProduct.Price));
            command.Parameters.Add(new SqlParameter("@PricePerUnit", theProduct.PricePerUnit));
            command.Parameters.Add(new SqlParameter("@UnitsInStock", theProduct.UnitsInStock));
            command.Parameters.Add(new SqlParameter("@Discount", theProduct.Discount));
            command.Parameters.Add(new SqlParameter("@Status", theProduct.Status));
            command.Parameters.Add(new SqlParameter("@NumberOrder", theProduct.NumberOrder));
            command.Parameters.Add(new SqlParameter("@DeletedFlag", theProduct.DeletedFlag));
            theProduct.NumberOrder = 1;
            theProduct.Status = false;
            command.Parameters.Add(new SqlParameter("@IsAdd", isAdd));
            DataProvider.ExecuteNoneQuery(command);
        }

        public static DataSet GetDetailsProductsByProductID(Int32 ProductID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetProductsByProductID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@ProductID", ProductID));
            return DataProvider.ExecuteDataSet(command);
        }

        // lat top san pham
        public static DataSet GetTopProduct()
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetTopProduct";
            command.CommandType = CommandType.StoredProcedure;

            return DataProvider.ExecuteDataSet(command);
        }
        // lay tat ca san pham

        public static DataSet GetAllProduct(Int32 pageIndex, Int32 pageSize, out Int32 totalRecord)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetAllProducts";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            command.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            DataSet ds = DataProvider.ExecuteDataSet(command, out totalRecord);
            return ds;
        }
        #endregion

    }
}